package com.ustcinfo.study.hive.r5.mouyiquan;

import java.sql.*;

/**
 * @Author: Yiquan Mou
 * @Email: ctbumyq@gmail.com
 * @Description:
 * @Date: Created in 19:33 2018/8/3
 */

public class HiveStatisticsHttp {

    // hive链接地址
    private static String url = "jdbc:hive2://nodex1:10000/default";

    //使用hive的用户名（这里不是root无法获取返回值）
    private static String userName = "root";

    // 使用hive的用户名密码
    private static String passwd = "000000";

    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";

    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;

    // 查询tables sql
    private static String showTableSql = "show tables";

    // 创建内部表dns_mouyiquan sql并且行格式分隔由“#”终止的字段
    private static String createSql = "create table dns_mouyiquan(id int,domain_name string,access_time string,target_ip string,status string)row format delimited fields terminated by '#' ";
    // 加载数据到dns_mouyiquan sql
    private static String localDataSql = "load data local inpath '/root/bigdata/tmp/dnsSample' into table dns_mouyiquan ";

    // 统计出dns_mouyiquan各域名对应的ip
    private static String countSql = "select domain_name,count(*) num from dns_mouyiquan group by domain_name order by num desc";
//    private static String countSql = "select domain_name,count(*) num from dns_mouyiquan group by domain_name";

    public static void main(String[] args) throws SQLException {

        try {
            //  加载hive驱动
            Class.forName(driver);
            // 连接hive
            connection = DriverManager.getConnection(url, userName, passwd);
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
            }

            statement = connection.createStatement();

            // 获取数据库中所有的表
            resultSet = statement.executeQuery(showTableSql);
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);

                if (tableName == null ){    //根据返回结果判断是否存在表
                     //执行建表语句
                    statement.execute(createSql);
                    //执行数据导入语句，需要保证文件存在
                    statement.execute(localDataSql);
                }else {
                    System.out.println(tableName);
                }
            }
            // 执行查询语句
            resultSet = statement.executeQuery(countSql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString(1) + "\t" + resultSet.getInt(2));
            }
        }catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
            if(connection != null) {
                connection.close();
            }

        }
    }
}
